import json
from django.http import HttpResponse
from django.db.models import Avg, Count, Min, Max, Sum, F, Q, Prefetch
from django.db import connection
from .models import Book, Author, BookOrder, Publisher


def index(request):
    # 1.获取价格大于100，并且评分在4.85分以上的图书
    # books = Book.objects.filter(price__gt=100, rating__gt=4.85).values('name', 'author__name', 'price', 'rating')
    # books = Book.objects.filter(Q(price__gt=100) & Q(rating__gt=4.85)).values('name', 'author__name', 'price', 'rating')
    # print(books)

    # 2.获取价格低于100元，或者评分低于4分的图书
    # books = Book.objects.filter(Q(price__lt=100) | Q(rating__lt=4)).values('name', 'author__name', 'price', 'rating')
    # print(books)

    # 3.获取价格大于100，并且图书名字中不包含”传“字的图书
    books = Book.objects.filter(Q(price__gt=100) & ~Q(name__icontains='传')). \
        values('name', 'author__name', 'price', 'rating')
    print(books)
    print(connection.queries[-1].get('sql'))
    return HttpResponse('查询成功')


def index2(request):
    result = Book.objects.aggregate(avg_price=Avg('price'))
    print(result)
    print(connection.queries)
    return HttpResponse('查询成功')


def index3(request):
    books = Book.objects.annotate(avg_price=Avg('bookorder__price')).values('name', 'author__name', 'price',
                                                                            'avg_price')
    for book in books:
        print(book)
    print(connection.queries)
    return HttpResponse('查询成功')


def index4(request):
    # 1.book表中总共有多少本书（book表中总共有多少个id）
    # result = Book.objects.aggregate(num=Count('name', distinct=True))
    # print(result)

    # 2.要统计作者表中总共有多少个不同的邮箱
    # result = Author.objects.aggregate(num=Count('email', distinct=True))
    # print(result)

    # 3.统计每本书的销量
    result = Book.objects.annotate(num=Count('bookorder')).values('name', 'author__name', 'num')
    print(result)

    print(connection.queries)
    return HttpResponse('查询成功')


def index5(request):
    # 1.查询作者中最大、最小年纪
    result = Author.objects.aggregate(min_age=Min('age'), max_age=Max('age'))
    print(result)
    # 2.查询每本书的最高、最低售价
    books = Book.objects.annotate(min_price=Min('bookorder__price'), max_price=Max('bookorder__price')). \
        values('name', 'author__name', 'min_price', 'max_price')
    for book in books:
        print(book)
    print(connection.queries)
    return HttpResponse('查询成功')


def index6(request):
    # 1. 所有图书成交总额
    # result = BookOrder.objects.aggregate(Sum('price'))
    # print(result)

    # 2.每本图书的成交总额
    # books = Book.objects.annotate(total=Sum('bookorder__price')).values('name', 'author__name', 'total')
    # for book in books:
    #     print(book)

    # 3.2021年所有图书成交总额
    # result = BookOrder.objects.filter(create_time__year=2021).aggregate(total=Sum('price'))
    # print(result)

    # 4.2021年每本图书成交总额
    books = Book.objects.filter(bookorder__create_time__year=2021). \
        annotate(total=Sum('bookorder__price')).values('name', 'author__name', 'total')
    for book in books:
        print(book)
    print(connection.queries)
    return HttpResponse('查询成功')


def index7(request):
    # 1.将所有图书的售价增加10元
    Book.objects.update(price=F('price') + 10)

    # 2.查询作者表name和email相同数据
    authors = Author.objects.filter(name__exact=F('email'))
    print(authors)
    print(connection.queries[-1])
    return HttpResponse('查询成功')


def query_set_demo(request):
    publishers = Publisher.objects.get_queryset()[5:10]
    print(publishers)
    print(json.dumps(connection.queries, ensure_ascii=False, indent=4))
    return HttpResponse('QuerySet API详解')


def query_set_demo1(request):
    # 1.filter(满足条件的数据提取出来)、exclude(排除满足条件的数据)
    # books = Book.objects.filter(id__gte=2).exclude(id=3).values('name', 'author__name', 'price', 'publisher__name')
    # print(books)

    # 2.annotate
    # TODO 未优化前
    books = Book.objects.all()
    # TODO 优化后
    books = Book.objects.annotate(author_name=F('author__name')).values('name', 'author_name')
    for book in books:
        print(book)
    print(json.dumps(connection.queries, ensure_ascii=False, indent=4))
    return HttpResponse('QuerySet API详解 - filter、exclude、annotate')


def query_set_demo2(request):
    # 1. 根据create_time从小到大进行排序
    book_orders = BookOrder.objects.values('price', 'create_time').order_by('create_time')
    # 2. 根据create_time从大到小进行排序
    book_orders = BookOrder.objects.values('price', 'create_time').order_by('-create_time')
    # 3. 首先根据create_time从大到小进行排序，如果create_time是一样的，那么根据price从大到小进行排序
    book_orders = BookOrder.objects.values('price', 'create_time').order_by('-create_time', '-price')
    # 4. 要根据订单的图书的评分来进行排序(从小到大)
    book_orders = Book.objects.values('name', 'rating').order_by('rating')
    # 5. 提取图书数据，根据图书的销量进行排序（从大到小进行排序）
    book_orders = Book.objects.annotate(sale=Count('bookorder')).order_by('-sale').values('name', 'author__name', 'sale')
    for book in book_orders:
        print(book)
    print(json.dumps(connection.queries, ensure_ascii=False, indent=4))
    return HttpResponse('QuerySet API详解 - filter、exclude、annotate、order_by')


def query_set_demo3(request):
    # 1.未传递values参数，默认查表下面所有字段
    books = Book.objects.values()
    # 2.查询指定字段
    books = Book.objects.values('name', 'price', 'pages', 'rating')
    # 3.查询关联字段
    books = Book.objects.values('name', 'price', 'pages', 'rating', author_name=F('author__name'))
    # 4.统计每本书的销量
    books = Book.objects.values('name', 'price', 'pages', 'rating',
                                author_name=F('author__name'), sale=Count('bookorder'))
    for book in books:
        print(book)
    print(json.dumps(connection.queries, ensure_ascii=False, indent=4))
    return HttpResponse('QuerySet API详解 - filter、exclude、annotate、order_by、values和values_list')


def query_set_demo4(request):
    books = Book.objects.select_related('author', 'publisher').all()
    for book in books:
        print(book.name, book.author.name, book.publisher.name)
    print(json.dumps(connection.queries, ensure_ascii=False, indent=4))
    return HttpResponse('QuerySet API详解 - filter、exclude、annotate、order_by、values和values_list、select_related')


def query_set_demo5(request):
    # TODO 传统方案查询图书订单
    # books = Book.objects.all()

    # TODO prefetch_related
    prefetch = Prefetch('bookorder_set')

    # TODO prefetch_related条件过滤
    prefetch = Prefetch('bookorder_set', BookOrder.objects.filter(price__gte=95))
    books = Book.objects.prefetch_related(prefetch)

    for book in books:
        book_orders = book.bookorder_set.all()
        print('=' * 30)
        for order in book_orders:
            print(f'{book.name}, {order.pk}, {order.price}')
    print(json.dumps(connection.queries, ensure_ascii=False, indent=4))
    return HttpResponse('QuerySet API详解')


def query_set_demo6(request):
    Publisher.objects.create(name='电子信息出版社')
    print(json.dumps(connection.queries, ensure_ascii=False, indent=4))
    return HttpResponse('QuerySet API详解')


def query_set_demo7(request):
    # publisher, created = Publisher.objects.get_or_create(name='电子信息出版社')
    # print(publisher)
    # print(created)
    Publisher.objects.bulk_create([
        Publisher(name='人民邮电出版社'),
        Publisher(name='机械工业出版社'),
        Publisher(name='电子工业出版社')
    ])
    print(json.dumps(connection.queries, ensure_ascii=False, indent=4))
    return HttpResponse('QuerySet API详解')


def query_set_demo8(request):
    # count
    count = Book.objects.annotate(sale=Count('bookorder')).filter(sale__gte=1).count()
    print(count)
    # exists
    exist = Publisher.objects.filter(name='123民邮电出版社').exists()
    print(exist)
    print(json.dumps(connection.queries, ensure_ascii=False, indent=4))
    return HttpResponse('QuerySet API详解')


def query_set_demo9(request):
    book_orders = BookOrder.objects.values('book_id').distinct()
    book_orders = BookOrder.objects.values('book_id').order_by('create_time').distinct()
    print(book_orders)
    print(json.dumps(connection.queries, ensure_ascii=False, indent=4))
    return HttpResponse('QuerySet API详解')